﻿using System;
using System.Collections.Generic;
using System.Text;
using BCMS.DAL.IDAL;
using MySql.Data.MySqlClient;
using System.Data;
using BCMS.Business.DataContract;

namespace BCMS.DAL.MySQL
{
    public class Logs : ILog
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region ILog Members

        #region Param
        private const string PARM_DATE = "@DATE";
        private const string PARM_USERID = "@USERID";
        private const string PARM_ACTION = "@ACTION";

        #endregion

        #region SQL        
        private const string LOG_INSERT = "INSERT INTO logs(date, userid, action) VALUES (now(), @userid, @action) ON DUPLICATE KEY UPDATE date=now(), userid=@userid, action=@action";

        private const string LOG_DELETE_BEFOREDATE = "DELETE FROM logs WHERE date<=@date";

        private const string LOG_DELETE_BYUSERID = "DELETE FROM logs WHERE userid=@userid";

        private const string LOG_SEARCH = "SELECT * FROM logs WHERE '1'='1' ";
        #endregion

        public void insertLog(LogInfo item)
        {
            try
            {
                /*MySqlParameter[] logParms = new MySqlParameter[]{ new MySqlParameter(PARM_DATE, MySqlDbType.DateTime),
                                                                      new MySqlParameter(PARM_USERID, MySqlDbType.VarChar),
                                                                      new MySqlParameter(PARM_ACTION, MySqlDbType.VarChar)
                };
                logParms[0].Value = item.Date;
                logParms[1].Value = item.UserId;
                logParms[2].Value = item.Action;*/

                MySqlParameter[] logParms = new MySqlParameter[]{ new MySqlParameter(PARM_USERID, MySqlDbType.VarChar),
                                                                  new MySqlParameter(PARM_ACTION, MySqlDbType.VarChar)
                };
                logParms[0].Value = item.UserId;
                logParms[1].Value = item.Action;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, LOG_INSERT, logParms);

            }
            catch
            {
                throw;
            }
        }

        public void clearLogBeforeDate(DateTime fromDate)
        {
            try
            {                
                MySqlParameter parm = new MySqlParameter(PARM_DATE, MySqlDbType.DateTime);                
                parm.Value = fromDate;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, LOG_DELETE_BEFOREDATE, parm);

            }
            catch
            {
                throw;
            }
        }

        public void clearLogByUserId(string userid)
        {
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_DATE, MySqlDbType.DateTime);
                parm.Value = userid;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, LOG_DELETE_BYUSERID, parm);

            }
            catch
            {
                throw;
            }
        }

        public List<LogInfo> searchLog(string username, string action, DateTime fromDate, DateTime toDate)
        {
            MySqlDataReader rdr = null;
            List<LogInfo> list = new List<LogInfo>();
            try
            {
                string query = LOG_SEARCH + buildUserSearch(username) + buildActionSearch(action) + buildDateSearch(fromDate, toDate);

                rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, query);

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(new LogInfo(Convert.ToDateTime(rdr["Date"]),
                                                rdr["userid"].ToString(),
                                                rdr["action"].ToString()));
                    return list;
                }
                else
                    return null;
            }
            catch 
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }

        private string buildUserSearch(string username)
        {
            if (string.IsNullOrEmpty(username))
                return string.Empty;
            else
                return " AND userid = '" + username + "'";
        }

        private string buildActionSearch(string action)
        {
            if (string.IsNullOrEmpty(action))
                return string.Empty;
            else
                return " AND userid = '" + action + "'";
        }

        private string buildDateSearch(DateTime fromDate, DateTime toDate)
        {
            if (fromDate > toDate)
                return string.Empty;
            else
                return " AND (date >= '" + fromDate.ToString("yyyy-MM-dd") + "') AND (date <= '" + toDate.ToString("yyyy-MM-dd") + "')";
        }

        #endregion
    }
}
